var express = require('express');
var router = express.Router();
let {isInteger} = require('../utils/utils');
let sequelize = require('../utils/sequelize');

/**
 * 数据库操作
 */
router.all('/', function (req, res, next) {

  //数据库操作
  let option = req.body.option;
  //数据库名
  let database = req.body.database;

  //过滤数据库为空情况
  if (!database) {
    res.send({"success": false, "msg": "数据库不存在"})
    return
  }

  //判断数据库操作
  switch (option) {
    case 'list':
      getList(req, res);
      break;
    case 'detail':
      getDetail(req, res);
      break;
    case 'add':
      addRecord(req, res);
      break;
    case 'update':
      updateRecord(req, res);
      break;
    case 'delete':
      deleteRecord(req, res);
      break;
    default:
      res.send({"success": false, "msg": "无效数据库操作"});
      return
  }


});

/**
 * 获得分页数据
 * @param req
 * @param res
 */
function getList(req, res) {
  //数据库名
  let database = req.body.database;
  //方案1 通过字段来取 page 页数 order 正序倒序  sortBy排序字段 limit 每页条数
  let {page, order, sortBy, limit} = req.body;

  //方案2 数据整合在json里
  // let data = req.body.data ? JSON.parse(req.body.data) : null;
  //
  // let {page, order, sortBy, limit} = data;


  //过滤掉无效数据
  if (!page || isNaN(page) || page < 1 || isInteger(page)) {
    page = 1;
  }
  if (!order || (order !== 'desc' && order !== 'asc')) {
    order = 'desc';
  }
  if (!sortBy) {
    sortBy = 'id';
  }
  //过滤不存在的 非数字的 小于0的
  if (!limit || isNaN(limit) || limit < 0 || isInteger(limit)) {
    limit = 20;
  }

  let offset = (page - 1) * limit;

  let sql = `SELECT * FROM ${database} ORDER BY ${sortBy} ${order} LIMIT ${offset},${limit}`;

  sequelize.query(sql).then(myTableRows => {
    console.log(myTableRows[0])
    res.send({"success": true, "msg": "成功", "data": myTableRows[0]});
    return
  })

}

/**
 * 根据id获得单条数据
 * @param req
 * @param res
 */
function getDetail(req, res) {
  //数据库名
  let database = req.body.database;
  //记录id
  let id = parseInt(req.body.id);

  //过滤无效数据
  if (!id || !isInteger(id) || id < 0) {
    res.send({"success": false, "msg": "参数错误"});
    return
  }

  let sql = `SELECT * FROM ${database} WHERE id = ${id} LIMIT 0,1`;

  sequelize.query(sql).then(myTableRows => {
    res.send({"success": true, "msg": "成功", "data": myTableRows[0]});
    return
  })
}

/**
 * 添加单条数据
 * @param req
 * @param res
 */
function addRecord(req, res) {
  //数据库名
  let database = req.body.database;
  //添加的记录的字段信息
  let data = JSON.parse(req.body.data);
  let keys = "";
  let values = "";

  if (!data) {
    res.send({"success": false, "msg": "参数错误"});
    return
  }

  //拼接键名和键值
  for (let i in data) {
    keys += i + ", ";
    if (isNaN(data[i])) {
      values += "'" + data[i] + "', ";
    } else {
      values += data[i] + ", ";
    }

  }

  keys = keys.substring(0, keys.length - 2);
  values = values.substring(0, values.length - 2);

  let sql = `INSERT INTO ${database} (${keys}) VALUES (${values})`;
  sequelize.query(sql).then(myTableRows => {
    if (myTableRows[0]) {
      //console.log(myTableRows[0].changedRows)
      res.send({"success": true, "msg": "成功", "data": myTableRows[0]});
      return
    }
  })
}

/**
 * 更新单条数据
 * @param req
 * @param res
 */
function updateRecord(req, res) {
  //数据库名
  let database = req.body.database;
  //添加的记录的字段信息
  let data = JSON.parse(req.body.data);
  //记录id
  let id = parseInt(req.body.id);
  let values = "";

  if (!id || !isInteger(id) || id < 0 || !data) {
    res.send({"success": false, "msg": "参数错误"});
    return
  }

  //拼接键值对
  for (let i in data) {
    if (isNaN(data[i])) {
      values += `${i} = '${data[i]}', `
    } else {
      values += `${i} = ${data[i]}, `
    }

  }

  values = values.substring(0, values.length - 2);

  let sql = `UPDATE ${database} SET ${values} WHERE id = ${id}`;
  sequelize.query(sql).then(myTableRows => {
    if (myTableRows[0].changedRows) {
      //console.log(myTableRows[0].changedRows)
      res.send({"success": true, "msg": "成功"});
      return
    }
  })
}

/**
 * 删除一条数据
 * @param req
 * @param res
 */
function deleteRecord(req, res) {
  //数据库名
  let database = req.body.database;
  //删除记录id
  let id = parseInt(req.body.id);

  //过滤无效id
  if (!id || !isInteger(id) || id < 0) {
    res.send({"success": false, "msg": "参数错误"});
    return
  }

  let sql = `DELETE FROM ${database} WHERE id = ${id}`;

  sequelize.query(sql).then(myTableRows => {
    console.log(myTableRows)
    if (myTableRows[0].changedRows) {
      //console.log(myTableRows[0].changedRows)
      res.send({"success": true, "msg": "成功"});
      return
    }
  })
}

module.exports = router;


